# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2Json
from com.zzh.utils.pagingUtils import MyPaging
import time
from com.zzh.utils.sqlUtils.SqlUtils import SqlUtils
from com.zzh.utils.requestFieldMapDBField import requestFieldMapToDBField
localTime = time.strftime('%Y-%m-%d', time.localtime(time.time()))
__author__ = "Prince_CHEN"


class ItemDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def insertItem(self, info):
        """
        添加 item 信息
        :param info:
        :return:
        """
        deptId = info['deptId']
        itemHandlerName = info['itemHandlerName']
        itemHandlerPhone = info['itemHandlerPhone']
        itemTitle = info['itemTitle']
        itemPriceRange = info['itemPriceRange']
        itemContent = info['itemContent']
        itemUrl = info['itemUrl']
        itemDeadline = info['itemDeadline']
        itemPublishdate = info['itemPublishdate']
        itemAddress = info['itemAddress']
        itemStatus = info['itemStatus']
        serviceId = info['serviceId']
        itemEnterPerson = info['itemEnterPerson']
        itemEnterTime = info['itemEnterTime']
        checkStatusOne = info['checkStatusOne']
        checkRemarkOne = info['checkRemarkOne']
        checkPersonOne = info['checkPersonOne']
        checkTimeOne = info['checkTimeOne']
        checkStatusTwo = info['checkStatusTwo']
        checkRemarkTwo = info['checkRemarkTwo']
        checkTimeTwo = info['checkTimeTwo']
        checkPersonTwo = info['checkPersonTwo']

        sql = 'insert into zzh_item (dept_id,item_handlername, item_handlerphone,item_title,item_pricerange,item_content,' \
              'item_url,item_deadline,item_publishdate,item_address,item_status,service_id, ' \
              'item_enter_person, item_enter_time,check_status_one,check_remark_one,check_person_one,check_time_one,' \
              'check_status_two,check_remark_two,check_person_two,check_time_two) ' \
              'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
        cur = self.conn.cursor()
        rs = cur.execute(sql, [deptId, itemHandlerName, itemHandlerPhone, itemTitle, itemPriceRange,
                               itemContent, itemUrl, itemDeadline, itemPublishdate, itemAddress, itemStatus, serviceId,
                               itemEnterPerson, itemEnterTime,checkStatusOne,checkRemarkOne,checkPersonOne,checkTimeOne,
                               checkStatusTwo, checkRemarkTwo,checkPersonTwo,checkTimeTwo])
        self.conn.commit()
        if rs:
            sql1 = "select * from zzh_item where item_id=(select max(item_id) from zzh_item)"
            rs = cur.execute(sql1)
            result = cur.fetchall()
            rsSet = Table2Json.item_list_to_json(result)
            return rs, rsSet
        cur.close()
        self.conn.close()
        return rs, None

    def findItemById(self, itemId):
        """
        根据ID查询 item 信息
        :param itemId:
        :return: json data
        """
        sql = "select * from zzh_item WHERE item_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [itemId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.item_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def findItemByCondition(self, **kwargs):
        """
        item 表的条件查询
        :param kwargs:
        :return:
        """

        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        fieldMap = requestFieldMapToDBField.itemFieldMap
        whereSentence  = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM zzh_item WHERE " + whereSentence + "  and check_status_one=2 AND check_status_two=2 AND item_status=2 ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM zzh_item WHERE " + whereSentence
        try:
            rs = cur.execute(sql)
        except:
            return -200, 0

        jsons = None
        if rs:
            rsSet = cur.fetchall()
            list0 = MyPaging().getPage(rs, pageIndex, pageSize, rsSet)
            jsons = Table2Json.item_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons, rs

    def deleteItem(self, idArray):
        """
        根据ID删除信息，可以传递一个或者多个ID，ID以数组形式传递
        :param idArray:
        :return:
        """
        sql = "delete from zzh_item WHERE item_id=%s"
        cur = self.conn.cursor()
        for idd in idArray:
            rs = cur.execute(sql, [idd])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def updateItem(self, info):
        """
        更新 item 表
        :param info: 
        :return:
        """
        itemId = info['itemId']
        deptId = info['deptId']
        itemHandlerName = info['itemHandlerName']
        itemHandlerPhone = info['itemHandlerPhone']
        itemTitle = info['itemTitle']
        itemPriceRange = info['itemPriceRange']
        itemContent = info['itemContent']
        itemUrl = info['itemUrl']
        itemDeadline = info['itemDeadline']
        itemPublishdate = info['itemPublishdate']
        itemAddress = info['itemAddress']
        itemStatus = info['itemStatus']
        serviceId = info['serviceId']
        itemEnterPerson = info['itemEnterPerson']
        itemEnterTime = info['itemEnterTime']
        checkStatusOne = info['checkStatusOne']
        checkRemarkOne = info['checkRemarkOne']
        checkPersonOne = info['checkPersonOne']
        checkTimeOne = info['checkTimeOne']
        checkStatusTwo = info['checkStatusTwo']
        checkRemarkTwo = info['checkRemarkTwo']
        checkTimeTwo = info['checkTimeTwo']
        checkPersonTwo = info['checkPersonTwo']

        sql = "update zzh_item set dept_id=%s,item_handlername=%s,item_handlerphone=%s,item_title=%s,item_pricerange=%s," \
              "item_content=%s,item_url=%s,item_deadline=%s,item_publishdate=%s,item_address=%s,item_status=%s,service_id=%s, " \
              "item_enter_person=%s, item_enter_time=%s, check_status_one=%s, check_remark_one=%s, check_person_one=%s, " \
              "check_time_one=%s, check_status_two=%s, check_remark_two=%s,check_time_two=%s, check_person_two=%s " \
              " WHERE item_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql,
                         [deptId, itemHandlerName, itemHandlerPhone, itemTitle, itemPriceRange, itemContent, itemUrl,
                          itemDeadline, itemPublishdate, itemAddress, itemStatus, serviceId, itemEnterPerson,itemEnterTime,
                          checkStatusOne, checkRemarkOne, checkPersonOne, checkTimeOne, checkStatusTwo, checkRemarkTwo,
                          checkTimeTwo, checkPersonTwo,
                          itemId])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def selectItem(self, itemId):
        """
        根据ID查询，返回元组
        :param itemId:
        :return: data tuple
        """
        sql = "select * from zzh_item WHERE item_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [itemId])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr

    def getMaxId(self):
        sql = "select max(item_id) from zzh_item"
        cur = self.conn.cursor()
        rs = cur.execute(sql)
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr[0]

    def updateItemStates(self):
        cur = self.conn.cursor()
        sql2 = 'UPDATE zzh_item SET item_status=0 WHERE item_deadline<%s AND item_status=1 '
        res = cur.execute(sql2, [localTime])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return res